import pandas as pd
from datetime import datetime

# 读取与预处理

def read():
    lines = pd.read_excel('./rawData/附件1.xlsx')  # 线路信息
    fleets = pd.read_excel('./rawData/附件5.xlsx') # 车队信息
    routes = pd.read_excel('./rawData/附件4.xlsx') # 串点站点
    preds  = pd.read_excel('./processedData/结果表1_预测结果.xlsx') # 预测结果
    return lines, fleets, routes, preds

# 构建双向串点对集

def pairs(routes):
    prs = set()
    for _, r in routes.iterrows():
        prs.add((r['站点编号1'], r['站点编号2']))
        prs.add((r['站点编号2'], r['站点编号1']))
    return prs

# 合并预测包裹量并格式化时间/编码

def prep(lines, preds):
    lines['time'] = pd.to_datetime(lines['发运节点'], format='%H:%M:%S').dt.time
    lines['date'] = '2024/12/16'
    preds['date'] = pd.to_datetime(preds['日期']).dt.strftime('%Y/%m/%d')
    lines['code'] = lines['线路编码'].str.strip()
    preds['code']  = preds['线路编码'].str.strip()
    # 映射包裹量
    def vol(x):
        m = preds[(preds['code']==x['code']) & (preds['date']==x['date'])]
        return m['货量'].iat[0] if not m.empty else 0
    lines['vol'] = lines.apply(vol, axis=1)
    return lines

# 判断是否可串点

def link(a, b, prs):
    if a['起始场地'] != b['起始场地']: return False
    if a['车队编码'] != b['车队编码']: return False
    dt = abs(datetime.combine(datetime.today(), a['time']) -
             datetime.combine(datetime.today(), b['time']))
    if dt.total_seconds() > 1800: return False
    if a['目的场地']!=b['目的场地'] and (a['目的场地'], b['目的场地']) not in prs:
        return False
    return True

# 构建串点链表

def build(lines, prs, cap=500):
    used = set()
    chains = []
    for _, x in lines.sort_values('time').iterrows():
        c = x['code']
        if c in used: continue
        grp = [x]
        load = x['vol']
        used.add(c)
        for _, y in lines.sort_values('time').iterrows():
            c2 = y['code']
            if c2 in used: continue
            if link(x, y, prs) and load + y['vol'] <= cap:
                grp.append(y)
                load += y['vol']
                used.add(c2)
        chains.append(grp)
    return chains

# 分配车辆，自有优先

def assign(chains, fleets, cap=500):
    pool = {t:cnt for t,cnt in zip(fleets['车队编码'], fleets['自有车数量'])}
    out = []
    vid = 1
    for grp in sorted(chains, key=lambda g: -len(g)):
        team = grp[0]['车队编码']
        typ  = '自有' if pool.get(team,0)>0 else '外部'
        if typ=='自有': pool[team]-=1
        load = sum(r['vol'] for r in grp)
        rate = load / cap
        for r in grp:
            out.append({
                'code': r['code'],
                'date': r['date'],
                'time': r['time'].strftime('%H:%M:%S'),
                'veh': f"{typ}-V{vid}",
                'type': typ,
                'rate': rate
            })
        vid += 1
    return pd.DataFrame(out)

# 主流程

def main():
    lines, fleets, routes, preds = read()
    prs    = pairs(routes)
    lines  = prep(lines, preds)
    chains = build(lines, prs)
    plan   = assign(chains, fleets)
    plan.to_excel('./processedData/调度结果_重构方案.xlsx', index=False)
    print('已保存至 processedData/调度结果_重构方案.xlsx')
    tgt    = ['场地3 - 站点83 - 0600','场地3 - 站点83 - 1400']
    print(plan[plan['code'].isin(tgt)])

if __name__ == '__main__':
    main()
